package com.aliang.book_java1.java_util;

import com.aliang.book_java1.java_util.po.EnterpriseInfo;
import com.aliang.book_java1.java_util.po.EnterpriseYearInfo;
import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.text.SimpleDateFormat;
import java.util.*;

public class ExportExcelUtil {

    public static Integer ENTERPRISE_INFO_LINE_START=0;//企业信息起始列(包含)
    public static Integer ENTERPRISE_INFO_LINE_END=9;//企业信息终止列(包含)

    public static Integer ENTERPRISE_YEAR_INFO_LINE_START=10;//企业年度信息起始列(包含)
    public static Integer ENTERPRISE_YEAR_NUM=4;//企业年度数（不含预估值）
    public static Integer ENTERPRISE_YEAR_YGZ_NUM=1;//企业年度预估值数
    public static Integer ENTERPRISE_YEAR_INFO_LINE_NUM=10;//企业年度信息字段数
    public static Integer ENTERPRISE_YEAR_YGZ_LINE_NUM=10;//企业年度预估值字段数


    public static Integer ENTERPRISE_REMARK_LINE=0;//企业信息备注列


    /*-----------项目----------------*/

    public static Integer ITEM_INFO_LINE_START=0;//项目信息起始列(包含)
    public static Integer ITEM_INFO_LINE_END=18;//项目信息终止列(包含)

    public static Integer ITEM_BUILD_STATE_LINE_START=19;//项目建设合规情况起始列(包含)
    public static Integer ITEM_BUILD_STATE_LINE_END=28;//项目建设合规情况终止列(包含)

    public static Integer ITEM_YEAR_INFO_LINE_START=29;//年度信息起始列
    public static Integer ITEM_YEAR_SJZ_NUM=1;//年度设计值个数
    public static Integer ITEM_YEAR_NUM=4;//项目年度个数
    public static Integer ITEM_YEAR_YGZ_NUM=1;//项目年度预估值个数
    public static Integer ITEM_YEAR_LINE_NUM=10;//年度数据字段数量


    public static Integer ITEM_PRODUCT_INFO_LINE_START=89;//产品信息起始列
    public static Integer ITEM_PRODUCT_NUM=0;//产品信息个数
    public static Integer ITEM_PRODUCT_LINE_NUM=9;//产品信息字段数量



    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) throws Exception{
            if(cell == null) return "";
            /*此处根据业务暂时全部返回string*/
            if(cell.getCellType() == CellType.STRING){
                return cell.getStringCellValue();
            }else if(cell.getCellType() == CellType.BOOLEAN){
                return String.valueOf(cell.getBooleanCellValue());
            }else if(cell.getCellType() == CellType.FORMULA){
                return cell.getCellFormula() ;
            }else if(cell.getCellType() == CellType.NUMERIC){
                //因为Excel存储日期以数值类型存储，所以当数据是数值类型时还要判断真的是数值还是日期
                //excel存储的数值是1900/1/1到今天的天数，所以需要单独计算，此处为了简单模板处理日期时最好采用文本格式
                String dateDataFormatString=cell.getCellStyle().getDataFormatString();
                if("yyyy/m/d".indexOf(dateDataFormatString)>=0){
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
                    return sdf.format(date);
                }else {
                    return String.valueOf(cell.getNumericCellValue());
                }

            }
            return "";
        /*return cell.getStringCellValue();*/

    }


    /*企业导入获取每列数据所属范围*/
    private String getEnterpriseLineArea(Integer lineNum){
        String areaLine="";
        if(ENTERPRISE_INFO_LINE_START<=lineNum&&lineNum<=ENTERPRISE_INFO_LINE_END){
            //企业信息列
            areaLine="enterpriseInfo";
        }else if(ENTERPRISE_YEAR_INFO_LINE_START<=lineNum&&lineNum< ENTERPRISE_YEAR_INFO_LINE_START
                +ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM){
            //企业年度信息
            areaLine="enterpriseYear";
        }else if(ENTERPRISE_YEAR_INFO_LINE_START +ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM
                <=lineNum&&lineNum<
                ENTERPRISE_YEAR_INFO_LINE_START +ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM+ENTERPRISE_YEAR_YGZ_LINE_NUM){
            //企业年度预估值信息
            areaLine="enterpriseYearYgz";
        } else if(lineNum==ENTERPRISE_YEAR_INFO_LINE_START +
                ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM+ENTERPRISE_YEAR_YGZ_LINE_NUM){
            //备注信息
            areaLine="remark";
        }

        return areaLine;
    }

    /*项目导入获取每列数据所属范围*/
    private String getItemLineArea(Integer lineNum){
        String areaLine="";
        if(ITEM_INFO_LINE_START<=lineNum&&lineNum<=ITEM_INFO_LINE_END){
            //项目信息列
            areaLine="itemInfo";
        }else if(ITEM_BUILD_STATE_LINE_START<=lineNum&&lineNum<=ITEM_BUILD_STATE_LINE_END){
            //项目建设合规情况
            areaLine="itemBuildState";
        }else if(ITEM_YEAR_INFO_LINE_START<=lineNum&&lineNum<
                ITEM_YEAR_INFO_LINE_START +ITEM_YEAR_SJZ_NUM*ITEM_YEAR_LINE_NUM){
            //项目年度设计值
            areaLine="itemYearSjz";
        } else if(ITEM_YEAR_INFO_LINE_START +ITEM_YEAR_SJZ_NUM*ITEM_YEAR_LINE_NUM
                <=lineNum&&lineNum<
                ITEM_YEAR_INFO_LINE_START +(ITEM_YEAR_SJZ_NUM+ITEM_YEAR_NUM)*ITEM_YEAR_LINE_NUM){
            //项目年度
            areaLine="itemYear";
        } else if(ITEM_YEAR_INFO_LINE_START +(ITEM_YEAR_SJZ_NUM+ITEM_YEAR_NUM)*ITEM_YEAR_LINE_NUM
                <=lineNum&&lineNum<
                ITEM_YEAR_INFO_LINE_START +(ITEM_YEAR_SJZ_NUM+ITEM_YEAR_NUM+ITEM_YEAR_YGZ_NUM)*ITEM_YEAR_LINE_NUM){
            //项目年度预估值
            areaLine="itemYearYgz";
        }else if(ITEM_PRODUCT_INFO_LINE_START
                <=lineNum&&lineNum<
                ITEM_PRODUCT_INFO_LINE_START +ITEM_PRODUCT_NUM*ITEM_PRODUCT_LINE_NUM){
            //System.out.println("--计算·1产品之--"+ITEM_YEAR_INFO_LINE_START +(ITEM_YEAR_SJZ_NUM+ITEM_YEAR_NUM+ITEM_YEAR_YGZ_NUM)*ITEM_YEAR_LINE_NUM);
            //产品值
            areaLine="itemProduct";
        }

        return areaLine;
    }


    /*----------------导入方法---------------------*/

    //导入企业表信息
    public String importExcelEnterprise(MultipartFile file) throws Exception{
        String resultStr="数据解析成功";
        Workbook workbook= new HSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        int count = sheet.getLastRowNum()+1;//总行数
        System.out.println("-----count------"+count);
        for(int i = 4; i < count;i++){
            //将每一行的信息保存起来
            Row row = sheet.getRow(i);
            if(row!=null){
                Boolean isStopExport=false;
                String[] enterpriseInfoArr=new String[ENTERPRISE_INFO_LINE_END-ENTERPRISE_INFO_LINE_START+1];//企业信息

                //List<String[]> enterpriseYearArrList= new ArrayList<>(ENTERPRISE_YEAR_NUM);//企业年度信息
                String[][] enterpriseYearArrList=new String[ENTERPRISE_YEAR_NUM][ENTERPRISE_YEAR_INFO_LINE_NUM];//企业年度信息

                String[] enterpriseYearTgzArr=new String[ENTERPRISE_YEAR_YGZ_LINE_NUM];//企业年度预估值信息

                String remark="";
                //循环遍历每一列
                for(int j=0;j<=(ENTERPRISE_YEAR_INFO_LINE_START +
                        ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM+ENTERPRISE_YEAR_YGZ_LINE_NUM);j++){
                    String lineContent="";
                    try {
                        lineContent=getCellValue(row.getCell(j));
                    }catch (Exception e){
                        resultStr="第"+i+"行,第"+j+"列--：数据解析失败";
                        isStopExport=true;
                        break;
                    }

                    switch (getEnterpriseLineArea(j)){
                        case "enterpriseInfo":
                            //企业信息
                            enterpriseInfoArr[j]=lineContent;
                            break;
                        case "enterpriseYear":
                            //年度数据有多条，用循环来处理
                            Integer num=(j-ENTERPRISE_YEAR_INFO_LINE_START)/ENTERPRISE_YEAR_INFO_LINE_NUM;
                            Integer num2=(j-ENTERPRISE_YEAR_INFO_LINE_START)%ENTERPRISE_YEAR_INFO_LINE_NUM;
                            enterpriseYearArrList[num][num2]=lineContent;
                            break;
                        case "enterpriseYearYgz":
                            //年度数据有多条，用循环来处理
                            //System.out.println(j+"=enterpriseYearYgz="+lineContent);
                            Integer numYgz=j-(ENTERPRISE_YEAR_INFO_LINE_START +ENTERPRISE_YEAR_NUM*ENTERPRISE_YEAR_INFO_LINE_NUM);
                            enterpriseYearTgzArr[numYgz]=lineContent;
                            break;
                        case "remark":
                            //System.out.println(j+"=lineContent="+lineContent);
                            remark=lineContent;
                            break;
                        default:
                            break;

                    }

                }
                if(isStopExport){
                    break;
                }
                System.out.println("-----企业信息---"+ JSON.toJSONString(enterpriseInfoArr));
                System.out.println("-----年度信息---"+JSON.toJSONString(enterpriseYearArrList));
                System.out.println("-----预估值---"+JSON.toJSONString(enterpriseYearTgzArr));
                System.out.println("-----备注---"+remark);
                /*企业信息*/
                EnterpriseInfo enterpriseInfo=new EnterpriseInfo("", enterpriseInfoArr[0],enterpriseInfoArr[1], enterpriseInfoArr[2],
                        enterpriseInfoArr[3],enterpriseInfoArr[4], enterpriseInfoArr[5], enterpriseInfoArr[6], enterpriseInfoArr[7],
                        enterpriseInfoArr[8], enterpriseInfoArr[9], remark);
                /*年度信息*/
                List<EnterpriseYearInfo>  enterpriseYearInfos=new ArrayList<>();
                /*表年度是固定的这边就按照固定的处理，否则要去列头获取，更麻烦*/
                Map<Integer,String> map=new HashMap<>();
                map.put(0,"2015");
                map.put(1,"2020");
                map.put(2,"2021");
                map.put(3,"2022");
                Integer yearKey=0;
                for (String[] strArr:enterpriseYearArrList){
                    String year=map.get(yearKey);
                    yearKey++;
                    EnterpriseYearInfo enterpriseYearInfo=new EnterpriseYearInfo(year,
                            strArr[0], strArr[1], strArr[2],
                            strArr[3], strArr[4], strArr[5], strArr[6],
                            strArr[7], strArr[8], strArr[9]);
                    enterpriseYearInfos.add(enterpriseYearInfo);

                }
                /*年度预估值*/
                EnterpriseYearInfo enterpriseYearYgzInfo=new EnterpriseYearInfo("2025",
                        enterpriseYearTgzArr[0], enterpriseYearTgzArr[1], enterpriseYearTgzArr[2],
                        enterpriseYearTgzArr[3], enterpriseYearTgzArr[4], enterpriseYearTgzArr[5], enterpriseYearTgzArr[6],
                        enterpriseYearTgzArr[7], enterpriseYearTgzArr[8], enterpriseYearTgzArr[9]);

                System.out.println("-----企业信息-po--"+ JSON.toJSONString(enterpriseInfo));
                System.out.println("-----年度信息-po--"+JSON.toJSONString(enterpriseYearInfos));
                System.out.println("-----预估值-po--"+JSON.toJSONString(enterpriseYearYgzInfo));




            }else {
                resultStr="第"+i+"行--：请不要上传带有格式的空行文件";
                break;

            }

        }
        return resultStr;

    }

    /*递归获取产品数量*/
    private Integer getProductNum(Row row,Integer lineNum) throws Exception{
        Integer lastNum=0;

        Integer addNum=lineNum*ITEM_PRODUCT_LINE_NUM;//本次增加的行数
        System.out.println("-----lineIndex-----"+(ITEM_PRODUCT_INFO_LINE_START+addNum));
        String s3=getCellValue(row.getCell(ITEM_PRODUCT_INFO_LINE_START+addNum));
        /*递归方法一定要加阈值，防止陷入死循环，这里设置最多遍历10次，后面咨询实际产品量在做修改*/
        if(lineNum<10){
            if(StringUtils.isEmpty(s3)){
                lastNum=lineNum;
            }else {
                lineNum++;
                lastNum=getProductNum(row,lineNum);
            }
        }else {
            lastNum=lineNum;
        }
        return lastNum;
    }


    //导入项目表信息
    public String importExcelItem(MultipartFile file) throws Exception{
        String resultStr="数据解析成功";
        Workbook workbook= new HSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);    //获得第一个表单sheet
        int count = sheet.getLastRowNum()+1;//总行数
        System.out.println("-----count------"+count);
        /*第一步要递归获取产品数量*/
        Row rowTitle = sheet.getRow(1);
        Integer productNum=getProductNum(rowTitle,0);
        System.out.println("-productNum--productNum--"+productNum);
        ITEM_PRODUCT_NUM=productNum;
        for(int i = 4; i < count;i++){
            //将每一行的信息保存起来
            Row row = sheet.getRow(i);
            if(row!=null){
                Boolean isStopExport=false;
                String[] itemInfoArr=new String[ITEM_INFO_LINE_END-ITEM_INFO_LINE_START+1];//项目信息

                String[] itemBuildStateInfoArr=new String[ITEM_BUILD_STATE_LINE_END-ITEM_BUILD_STATE_LINE_START+1];//项目建设合规情况

                String[][] itemYearArrList=new String[ITEM_YEAR_NUM][ITEM_YEAR_LINE_NUM];//项目年度信息

                String[] itemYearSjzArr=new String[ITEM_YEAR_LINE_NUM];//项目年度设计值信息

                String[] itemYearYgzArr=new String[ITEM_YEAR_LINE_NUM];//项目年度预估值信息

                String[][] itemProductArrList=new String[ITEM_PRODUCT_NUM][ITEM_PRODUCT_LINE_NUM];//产品信息


                //循环遍历每一列（产品起始列加上产品个数*字段数，这个不需要等于，因为没有备注列）
                for(int j=0;j<(ITEM_PRODUCT_INFO_LINE_START+ITEM_PRODUCT_NUM*ITEM_PRODUCT_LINE_NUM);j++){
                    String lineContent="";
                    try {
                        lineContent=getCellValue(row.getCell(j));
                    }catch (Exception e){
                        resultStr="第"+i+"行,第"+j+"列--：数据解析失败";
                        isStopExport=true;
                        break;
                    }

                    switch (getItemLineArea(j)){
                        case "itemInfo":
                            //项目信息列
                            //System.out.println("----lineContent-----"+lineContent);
                            itemInfoArr[j]=lineContent;
                            break;
                        case "itemBuildState":
                            //项目建设合规情况
                            itemBuildStateInfoArr[j-ITEM_BUILD_STATE_LINE_START]=lineContent;
                            break;
                        case "itemYearSjz":
                            //项目年度设计值
                            itemYearSjzArr[j-ITEM_YEAR_INFO_LINE_START]=lineContent;
                            break;
                        case "itemYear":
                            //年度数据有多条，用循环来处理
                            Integer num=(j-(ITEM_YEAR_INFO_LINE_START +ITEM_YEAR_SJZ_NUM*ITEM_YEAR_LINE_NUM))/ITEM_YEAR_LINE_NUM;
                            Integer num2=(j-(ITEM_YEAR_INFO_LINE_START +ITEM_YEAR_SJZ_NUM*ITEM_YEAR_LINE_NUM))%ITEM_YEAR_LINE_NUM;
                            itemYearArrList[num][num2]=lineContent;
                            break;
                        case "itemYearYgz":
                            //项目年度预估值
                            //System.out.println(j+"=enterpriseYearYgz="+lineContent);
                            Integer numYgz=j-(ITEM_YEAR_INFO_LINE_START +(ITEM_YEAR_SJZ_NUM+ITEM_YEAR_NUM)*ITEM_YEAR_LINE_NUM);
                            itemYearYgzArr[numYgz]=lineContent;
                            break;
                        case "itemProduct":
                            //产品值数据有多条，用循环来处理
                            Integer numProduct=(j-ITEM_PRODUCT_INFO_LINE_START)/ITEM_PRODUCT_LINE_NUM;
                            Integer numProduct2=(j-ITEM_PRODUCT_INFO_LINE_START)%ITEM_PRODUCT_LINE_NUM;
                            itemProductArrList[numProduct][numProduct2]=lineContent;
                            break;
                        default:
                            break;

                    }

                }
                if(isStopExport){
                    break;
                }
                System.out.println("-----项目信息列---"+ JSON.toJSONString(itemInfoArr));
                System.out.println("-----项目建设合规情况---"+JSON.toJSONString(itemBuildStateInfoArr));
                System.out.println("-----项目年度设计值---"+JSON.toJSONString(itemYearSjzArr));
                System.out.println("-----年度数据---"+JSON.toJSONString(itemYearArrList));
                System.out.println("-----项目年度预估值---"+JSON.toJSONString(itemYearYgzArr));
                System.out.println("-----产品值数据---"+JSON.toJSONString(itemProductArrList));




            }else {
                resultStr="第"+i+"行--：请不要上传带有格式的空行文件";
                break;

            }

        }
        return resultStr;

    }



}
